package com.rickpan.repository;

import com.rickpan.entity.ProjectTask;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 项目任务数据访问层
 * 
 * @author RickPan Team
 * @since 2024-01-15
 */
@Repository
public interface ProjectTaskRepository extends JpaRepository<ProjectTask, Long> {

    /**
     * 根据项目ID查找任务列表
     */
    List<ProjectTask> findByProjectIdOrderByCreatedAtDesc(Long projectId);

    /**
     * 根据项目ID和状态查找任务列表
     */
    List<ProjectTask> findByProjectIdAndStatusOrderByCreatedAtDesc(Long projectId, ProjectTask.Status status);

    /**
     * 根据项目ID查找任务列表（分页）
     */
    Page<ProjectTask> findByProjectIdOrderByCreatedAtDesc(Long projectId, Pageable pageable);

    /**
     * 根据项目ID和状态查找任务列表（分页）
     */
    Page<ProjectTask> findByProjectIdAndStatusOrderByCreatedAtDesc(Long projectId, ProjectTask.Status status, Pageable pageable);

    /**
     * 根据分配者ID查找任务列表
     */
    List<ProjectTask> findByAssigneeIdOrderByDueDateAscCreatedAtDesc(Long assigneeId);

    /**
     * 根据分配者ID和状态查找任务列表
     */
    List<ProjectTask> findByAssigneeIdAndStatusOrderByDueDateAscCreatedAtDesc(Long assigneeId, ProjectTask.Status status);

    /**
     * 根据创建者ID查找任务列表
     */
    List<ProjectTask> findByCreatorIdOrderByCreatedAtDesc(Long creatorId);

    /**
     * 根据创建者ID和状态查找任务列表
     */
    List<ProjectTask> findByCreatorIdAndStatusOrderByCreatedAtDesc(Long creatorId, ProjectTask.Status status);

    /**
     * 根据项目ID统计任务数量
     */
    long countByProjectId(Long projectId);

    /**
     * 根据项目ID和状态统计任务数量
     */
    long countByProjectIdAndStatus(Long projectId, ProjectTask.Status status);

    /**
     * 根据分配者ID统计任务数量
     */
    long countByAssigneeId(Long assigneeId);

    /**
     * 根据分配者ID和状态统计任务数量
     */
    long countByAssigneeIdAndStatus(Long assigneeId, ProjectTask.Status status);

    /**
     * 查找即将到期的任务
     */
    @Query("SELECT t FROM ProjectTask t WHERE t.dueDate BETWEEN :startDate AND :endDate " +
           "AND t.status NOT IN ('COMPLETED', 'CANCELLED') ORDER BY t.dueDate ASC")
    List<ProjectTask> findTasksDueSoon(@Param("startDate") LocalDateTime startDate, 
                                      @Param("endDate") LocalDateTime endDate);

    /**
     * 查找逾期任务
     */
    @Query("SELECT t FROM ProjectTask t WHERE t.dueDate < :currentDate " +
           "AND t.status NOT IN ('COMPLETED', 'CANCELLED') ORDER BY t.dueDate ASC")
    List<ProjectTask> findOverdueTasks(@Param("currentDate") LocalDateTime currentDate);

    /**
     * 根据项目ID查找逾期任务
     */
    @Query("SELECT t FROM ProjectTask t WHERE t.projectId = :projectId AND t.dueDate < :currentDate " +
           "AND t.status NOT IN ('COMPLETED', 'CANCELLED') ORDER BY t.dueDate ASC")
    List<ProjectTask> findOverdueTasksByProject(@Param("projectId") Long projectId, 
                                               @Param("currentDate") LocalDateTime currentDate);

    /**
     * 根据分配者ID查找逾期任务
     */
    @Query("SELECT t FROM ProjectTask t WHERE t.assigneeId = :assigneeId AND t.dueDate < :currentDate " +
           "AND t.status NOT IN ('COMPLETED', 'CANCELLED') ORDER BY t.dueDate ASC")
    List<ProjectTask> findOverdueTasksByAssignee(@Param("assigneeId") Long assigneeId, 
                                                @Param("currentDate") LocalDateTime currentDate);

    /**
     * 根据项目ID搜索任务
     */
    @Query("SELECT t FROM ProjectTask t WHERE t.projectId = :projectId " +
           "AND (t.title LIKE %:keyword% OR t.description LIKE %:keyword%) " +
           "ORDER BY t.updatedAt DESC")
    List<ProjectTask> searchTasksByProject(@Param("projectId") Long projectId, 
                                          @Param("keyword") String keyword);

    /**
     * 根据项目ID搜索任务（分页）
     */
    @Query("SELECT t FROM ProjectTask t WHERE t.projectId = :projectId " +
           "AND (t.title LIKE %:keyword% OR t.description LIKE %:keyword%) " +
           "ORDER BY t.updatedAt DESC")
    Page<ProjectTask> searchTasksByProject(@Param("projectId") Long projectId, 
                                          @Param("keyword") String keyword, 
                                          Pageable pageable);

    /**
     * 根据优先级查找任务
     */
    List<ProjectTask> findByProjectIdAndPriorityOrderByCreatedAtDesc(Long projectId, ProjectTask.Priority priority);

    /**
     * 查找最近更新的任务
     */
    @Query("SELECT t FROM ProjectTask t WHERE t.projectId = :projectId ORDER BY t.updatedAt DESC")
    List<ProjectTask> findRecentTasksByProject(@Param("projectId") Long projectId, Pageable pageable);

    /**
     * 获取项目任务状态统计
     */
    @Query("SELECT t.status as status, COUNT(t) as count FROM ProjectTask t " +
           "WHERE t.projectId = :projectId GROUP BY t.status")
    List<TaskStatusCount> getTaskStatusStatsByProject(@Param("projectId") Long projectId);

    /**
     * 获取分配者任务状态统计
     */
    @Query("SELECT t.status as status, COUNT(t) as count FROM ProjectTask t " +
           "WHERE t.assigneeId = :assigneeId GROUP BY t.status")
    List<TaskStatusCount> getTaskStatusStatsByAssignee(@Param("assigneeId") Long assigneeId);

    /**
     * 根据项目ID和分配者ID查找任务
     */
    List<ProjectTask> findByProjectIdAndAssigneeIdOrderByCreatedAtDesc(Long projectId, Long assigneeId);

    /**
     * 根据项目ID和分配者ID查找任务（分页）
     */
    Page<ProjectTask> findByProjectIdAndAssigneeIdOrderByCreatedAtDesc(Long projectId, Long assigneeId, Pageable pageable);

    /**
     * 任务状态统计接口
     */
    interface TaskStatusCount {
        ProjectTask.Status getStatus();
        Long getCount();
    }
}
